package com.future.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.future.dbutils.TransactionUtils;
import com.future.model.po.OfficeModel;
import com.future.model.po.UserModel;

public class OfficeDAO extends BaseDAO<OfficeModel> {
    private static Logger logger = LogManager.getLogger(OfficeDAO.class);

	public OfficeDAO() {

	}

	public OfficeDAO(Connection conn) {
		super(conn);
	}

	public boolean save(OfficeModel office) {
		String sql = "insert into office (office,userid) values (?,?)";
		return this.update(sql, office.getOffice(), office.getUserid());
	}

	public boolean save(String officeName,
											Integer userId) {
		OfficeModel officeModel = new OfficeModel();
		officeModel.setOffice(officeName);
		officeModel.setUserid(userId);
		return this.save(officeModel);
	}

	public List<OfficeModel> findAll() {
		String sql = "select * from office";
		return this.searchAsBeanList(sql, OfficeModel.class);
	}

	public List<OfficeModel> findByUserId(Integer userId) {
		OfficeModel officeModel = new OfficeModel();
		officeModel.setUserid(userId);
		return this.findByUserId(officeModel);
	}

	public List<OfficeModel> findByUserId(OfficeModel officeModel) {
		String sql = "select * from office where userid = ?";
		return this.searchAsBeanList(sql, OfficeModel.class, officeModel.getUserid());
	}
	
	
	/**
	 * 判断是否添加过 处室
	 * @param model
	 * @return  true 表示当前用户添加过   false 表示当前用户没有添加过
	 */
	@Deprecated
	public boolean isOrExist(UserModel model) {
		String sql = "select * from office where userid = ? ";
		List<OfficeModel> list = this.searchAsBeanList(sql, OfficeModel.class,
				model.getId());
		if (list != null && list.size() > 0) {
			return true;
		} else {
			return false;
		}
	}
	@Deprecated
	public boolean updateOffice(List<OfficeModel> officeModels){
		boolean flag = false;
		String sql = "update office set office=? where userid=? and id=?";
		if (officeModels!=null&&officeModels.size()>0) {
			for (OfficeModel officeModel : officeModels) {
				flag = this.update(sql, officeModel.getOffice(),officeModel.getUserid(),
						officeModel.getId());
				if(!flag)
					break;
			}
		}
		return flag;
	}
	
	/**
	 * 删除第二部分  第四题 表格部分内容
	 * @param model
	 */
	public void deleteOffice(UserModel model) {
		Connection conn = null;
		PreparedStatement pstm = null;
		String del = "delete from office where userid=?";
		String sql = "delete  from officeservices where officeid  in  (select id from office where userid=?)";
		logger.info("删除处室del="+del);
		logger.info("删除处室业务sql=" + sql);
		try {
			conn = TransactionUtils.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, model.getId());
			pstm.executeUpdate();
			pstm.clearParameters();
			
			pstm = conn.prepareStatement(del);
			pstm.setInt(1, model.getId());
			pstm.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (pstm!=null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			TransactionUtils.closeConnection();
		}
	}
	
	/**
	 * 保存处室名称   
	 * @param officeModels
	 * @return  返回 主键
	 */
	public List<Integer> saveOffice(List<OfficeModel> officeModels){
		 List<Integer> list = new ArrayList<Integer>();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = "insert into office (office,userid) values (?,?)";
		logger.info("保存处室sql="+sql);
		try {
			if (officeModels!=null&&officeModels.size()>0) {
				for (OfficeModel officeModel : officeModels) {
					conn = TransactionUtils.getConnection();
					pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					pstm.setString(1, officeModel.getOffice());
					pstm.setInt(2, officeModel.getUserid());
					pstm.executeUpdate();
					rs = pstm.getGeneratedKeys();
					while (rs!=null&&rs.next()) {
						list.add(Integer.valueOf(String.valueOf(rs.getLong(1))));
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstm!=null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			TransactionUtils.closeConnection();
		}
		
		return list;
	}
	
	
	public List<OfficeModel> countOfficeByAreaId(Integer areaId){
		String sql = "select * from office where userid in (select id from user where areaid = ? )";
		return this.searchAsBeanList(sql, OfficeModel.class, areaId);
	}
	/**
	 * @author petter 20140224
	 * @param areaId
	 * @return
	 */
	public List<OfficeModel> countOfficeServicesByAreaId(Integer areaId){
		String sql = "select o.id,o.office,o.userid from office o where o.userid in (select id from user where areaid = ? ) and o.id in (select officeid  from officeservices where issupport=1)";
		return this.searchAsBeanList(sql, OfficeModel.class, areaId);
	}
}
